﻿using System.Data;
using SemiCASE.DataAccess;
using SemiCASE.DataType.Common.Attributes;
using System;

namespace SemiCASE.Business
{
    [ServiceConnectionNameAttribute("SemiCASEConnectionString")]
    public class LoginBS : BusinessBase
    {
        public DataTable IsUserValid(string pKullaniciAdi, string pSifre)
        {
            DataTable ds = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"SELECT 
                                   *
                               FROM [SemiCaseDB].[dbo].[USER]
                               WHERE UserName = @UserName AND Password = @Password";

            data.AddSqlParameter("UserName", pKullaniciAdi, SqlDbType.VarChar);
            data.AddSqlParameter("Password", pSifre, SqlDbType.VarChar);
            data.GetRecords(ds, sqlText);

            return ds;
        }

        public void InsertUser(string pKullaniciAdi, string pSifre, bool pIsAdmin, string pName, string pSurname, string pEmail)
        {
            IData data = GetDataObject();
            string sqlText = @"INSERT INTO [SemiCaseDB].[dbo].[USER]
                                       ([UserID]
                                       ,[UserName]
                                       ,[Password]
                                       ,[IsAdmin]
                                        ,[Name]
                                            ,[Surname]
                                            ,[Email])
                                 VALUES
                                       (NEWID()
                                       ,@UserName
                                       ,@Password
                                       ,@IsAdmin
                                       ,@Name
                                       ,@Surname
                                       ,@Email)";

            data.AddSqlParameter("UserName", pKullaniciAdi, SqlDbType.VarChar);
            data.AddSqlParameter("Password", pSifre, SqlDbType.VarChar);
            data.AddSqlParameter("IsAdmin", pIsAdmin, SqlDbType.Bit);
            data.AddSqlParameter("Name", pName, SqlDbType.VarChar);
            data.AddSqlParameter("Surname", pSurname, SqlDbType.VarChar);
            data.AddSqlParameter("Email", pEmail, SqlDbType.VarChar);
            data.ExecuteStatement(sqlText);
        }

        public void UpdateUser(Guid pUserID, string pKullaniciAdi, string pSifre, bool pIsAdmin, string pName, string pSurname, string pEmail)
        {
            IData data = GetDataObject();
            string sqlText = @"UPDATE [SemiCaseDB].[dbo].[USER]
                               SET [UserName] = @UserName
                                  ,[Password] = @Password
                                  ,[IsAdmin] = @IsAdmin
                                  ,[Name] = @Name
                                  ,[Surname] = @Surname
                                  ,[Email] = @Email
                             WHERE UserID = @UserID";

            data.AddSqlParameter("UserID", pUserID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("UserName", pKullaniciAdi, SqlDbType.VarChar);
            data.AddSqlParameter("Password", pSifre, SqlDbType.VarChar);
            data.AddSqlParameter("IsAdmin", pIsAdmin, SqlDbType.Bit);
            data.AddSqlParameter("Name", pName, SqlDbType.VarChar);
            data.AddSqlParameter("Surname", pSurname, SqlDbType.VarChar);
            data.AddSqlParameter("Email", pEmail, SqlDbType.VarChar);
            data.ExecuteStatement(sqlText);
        }

        public DataTable GetUserInfo(Guid pUserID)
        {
            DataTable ds = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"SELECT 
                                   *
                               FROM [SemiCaseDB].[dbo].[USER]
                               WHERE UserID = @UserID";

            data.AddSqlParameter("UserID", pUserID, SqlDbType.UniqueIdentifier);
            data.GetRecords(ds, sqlText);
            return ds;
        }

        public void DeleteUser(Guid pUserID)
        {
            DataTable ds = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"DELETE FROM [SemiCaseDB].[dbo].[USER]
                               WHERE UserID = @UserID";

            data.AddSqlParameter("UserID", pUserID, SqlDbType.UniqueIdentifier);
            data.GetRecords(ds, sqlText);
        }

        public DataTable ListUser()
        {
            DataTable ds = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"SELECT 
                                   *
                                   , '' AS SiraNo
                               FROM [SemiCaseDB].[dbo].[USER]";

            data.GetRecords(ds,sqlText);
            return ds;
        }
    }
}
